// server.js
const express = require("express");
const sql = require("mssql");
const cors = require("cors");

const app = express();
const PORT = 3001;

// 配置CORS以支持移动端访问
app.use(cors({
  origin: [
    'http://localhost:5173', 
    'http://localhost:5174',
    'capacitor://localhost', 
    'https://localhost',
    'http://localhost',
    'http://192.168.12.253:82',
    'https://192.168.12.253:82'
  ],
  credentials: true,
  methods: ['GET', 'POST', 'PUT', 'DELETE', 'OPTIONS'],
  allowedHeaders: ['Content-Type', 'Authorization', 'X-Requested-With', 'Origin', 'Accept']
}));
app.use(express.json());

/* ======  数据源 1：UFDATA_004_2025  ====== */
const sqlConfig1 = {
  user: "barcode",
  password: "p@ssw0rd",
  server: "192.168.12.252",
  database: "UFDATA_004_2025",
  pool: { max: 10, min: 0 },
  options: { trustServerCertificate: true, encrypt: false },
};

/* ======  数据源 2：ERP_Related  ====== */
const sqlConfig2 = {
  user: "Erp_Related",
  password: "123456", // 改成你的真实密码
  server: "192.168.12.253",
  database: "ERP_Related",
  pool: { max: 10, min: 0 },
  options: { trustServerCertificate: true, encrypt: false },
};

/* ======  创建两个独立连接池  ====== */
const pool1 = new sql.ConnectionPool(sqlConfig1);
const pool2 = new sql.ConnectionPool(sqlConfig2);

/* 启动时一次性连接 */
Promise.all([pool1.connect(), pool2.connect()])
  .then(() => console.log("✅ 双池连接成功"))
  .catch((err) => {
    console.error("❌ 连接失败", err);
    process.exit(1);
  });

/* ==========  查询接口（带扫描状态）  ========== */
app.post("/api/query", async (req, res) => {
  const {
    page = 1,
    size = 15,
    inspectionDateStart,
    inspectionDateEnd,
    produceDateStart,
    produceDateEnd,
    batchNo,
    bizType,
    vendorName,
  } = req.body;

  try {
    /* ---- 调试：确认连到正确库 ---- */
    const db = (await pool2.request().query("SELECT DB_NAME() AS db"))
      .recordset[0].db;
    const tbl = (
      await pool2
        .request()
        .query(
          "SELECT COUNT(*) AS c FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='erp_barcode'"
        )
    ).recordset[0].c;
    // console.log("253 当前库名：", db, "表是否存在：", tbl);

    const offset = (page - 1) * size;

    let where = "1=1";
    if (inspectionDateStart && inspectionDateEnd)
      where += ` AND 检验日期 BETWEEN '${inspectionDateStart}' AND '${inspectionDateEnd}'`;
    if (produceDateStart && produceDateEnd)
      where += ` AND 生产日期 BETWEEN '${produceDateStart}' AND '${produceDateEnd}'`;
    if (batchNo) where += ` AND 批号 LIKE '%${batchNo}%'`;
    if (bizType) where += ` AND 业务类型 = '${bizType}'`;
    if (vendorName) where += ` AND 供应商名称 = '${vendorName}'`;

    /* ---- 主数据（来源 252） ---- */
    const totalResult = await pool1
      .request()
      .query(`SELECT COUNT(*) AS total FROM dbo.CHECK_VIEW WHERE ${where}`);
    const total = totalResult.recordset[0].total;

    const sqlStr = `
      SELECT
        单据条码,
        检验单号,
        业务类型,
        存货编码,
        存货名称,
        规格型号,
        品牌,
        计量单位,
        供应商名称,
        批号,
        CONVERT(varchar(10), 检验日期, 120) AS 检验日期,
        CONVERT(varchar(10), 生产日期, 120) AS 生产日期,
        合格数量,
        不良数量
      FROM dbo.CHECK_VIEW
      WHERE ${where}
      ORDER BY 检验日期 DESC
      OFFSET ${offset} ROWS FETCH NEXT ${size} ROWS ONLY`;
    const main = await pool1.request().query(sqlStr);
    const list = main.recordset;

    /* ---- 扫描状态（来源 253） ---- */
    const barcodes = list.map((r) => `'${r.单据条码}'`).join(",");
    const scannedMap = {};
    if (barcodes) {
      const scannedRes = await pool2
        .request()
        .query(
          `SELECT barcode, is_scanned FROM dbo.erp_barcode WHERE barcode IN (${barcodes})`
        );
      scannedRes.recordset.forEach((r) => {
        scannedMap[r.barcode] = r.is_scanned;
      });
    }
    list.forEach((r) => {
      r.isScanned = scannedMap[r.单据条码] === 1;
    });

    res.json({ list, total });
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: e.message });
  }
});

/* ==========  批量标记扫描  ========== */
app.post("/api/markScanned", async (req, res) => {
  const { barcodes } = req.body;
  if (!Array.isArray(barcodes) || !barcodes.length)
    return res.status(400).json({ error: "barcodes 为空" });

  const tx = pool2.transaction();
  try {
    await tx.begin();
    for (const code of barcodes) {
      await tx.request().input("bc", sql.VarChar, code).query(`
          IF NOT EXISTS (SELECT 1 FROM dbo.erp_barcode WHERE barcode = @bc)
            INSERT INTO dbo.erp_barcode (barcode, is_scanned, create_time, update_time)
            VALUES (@bc, 1, GETDATE(), GETDATE())
          ELSE
            UPDATE dbo.erp_barcode
            SET is_scanned = 1, update_time = GETDATE()
            WHERE barcode = @bc
        `);
    }
    await tx.commit();
    res.json({ ok: true });
  } catch (e) {
    await tx.rollback();
    res.status(500).json({ error: e.message });
  }
});

/* ==========  批量取消扫描  ========== */
app.post("/api/markUnScanned", async (req, res) => {
  const { barcodes } = req.body;
  if (!Array.isArray(barcodes) || !barcodes.length)
    return res.status(400).json({ error: "barcodes 为空" });

  const tx = pool2.transaction();
  try {
    await tx.begin();
    for (const code of barcodes) {
      await tx.request().input("bc", sql.VarChar, code).query(`
          IF NOT EXISTS (SELECT 1 FROM dbo.erp_barcode WHERE barcode = @bc)
            INSERT INTO dbo.erp_barcode (barcode, is_scanned, create_time, update_time)
            VALUES (@bc, 0, GETDATE(), GETDATE())
          ELSE
            UPDATE dbo.erp_barcode
            SET is_scanned = 0, update_time = GETDATE()
            WHERE barcode = @bc
        `);
    }
    await tx.commit();
    res.json({ ok: true });
  } catch (e) {
    await tx.rollback();
    res.status(500).json({ error: e.message });
  }
});

/* ==========  供应商模糊搜索（POST）  ========== */
app.post("/api/vendor", async (req, res) => {
  const { kw = "" } = req.body; // 从 body 取关键字
  try {
    const result = await pool1
      .request()
      .input("kw", sql.VarChar, `%${kw}%`)
      .query(
        "SELECT DISTINCT [供应商名称] AS name " +
          "FROM dbo.CHECK_VIEW " +
          "WHERE [供应商名称] LIKE @kw " +
          "ORDER BY [供应商名称]"
      );
    res.json(result.recordset.map((r) => r.name)); // 仍然返回字符串数组
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

/* ==========  启动  ========== */
app.listen(PORT, '0.0.0.0', () =>
  console.log(`Server running at http://0.0.0.0:${PORT}`)
);
